package com.bjsxt.dao.impl;

import com.bjsxt.commons.JdbcUtils;
import com.bjsxt.dao.UserManager;
import com.bjsxt.pojo.Users;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

//用户管理持久层  写JDBC语号  将数据写入数据库
public class UserManagerImpl implements UserManager {
    //添加用户
    @Override
    public void insertUser(Users users) {
        Connection conn = null;
        try{
            conn= JdbcUtils.getConnection();
            //可以自动提交，但是设置为手动提交，让我们有事务的概念
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("insert into users values(default ,?,?,?,?,?) ");
            ps.setString(1,users.getUsername());
            ps.setString(2,users.getUserpwd());
            ps.setString(3,users.getUsersex());
            ps.setString(4,users.getPhonenumber());
            ps.setString(5,users.getQqnumber());
            ps.execute();
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtils.rollbackConnection(conn);//提交失败后回滚事务
        }finally {
            JdbcUtils.closeConnection(conn);
        }
    }

    //查询用户  查询用户可以根据多种条件查询（用户id,用户姓名....）所以创建一个方法拼接sql语句
    @Override
    public List<Users> selectUserByProperty(Users users) {
        Connection conn = null;
        //因为要返回list<user> 所以在外面定义list
        List<Users> list = new ArrayList<>();
        try{
            conn=JdbcUtils.getConnection();
            String sql = this.createSql(users);
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                //返回数据可能是多条 所以在这里实例化users
                Users user = new Users();
                //ORM映射
                user.setUserid(resultSet.getInt("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setUserpwd(resultSet.getString("userpwd"));
                user.setUsersex(resultSet.getString("usersex"));
                user.setPhonenumber(resultSet.getString("phonenumber"));
                user.setQqnumber(resultSet.getString("qqnumber"));
                list.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
        }

        return list;
    }



    //拼接sql语句  返回String类型
    private String createSql(Users users){
        StringBuffer stringBuffer  = new StringBuffer("select * from users where 1=1 ");
        //userid 与 userpwd 不可以作为查询条件
        //从表单中获取的字段，如果没有这字段也是空串 ，所以要长度大于0
        if(users.getUsername() != null && users.getUsername().length() >0){
            //注意空格 隔开 否则语句错误
            stringBuffer.append(" and username = "+users.getUsername());
        }
        if(users.getQqnumber() != null && users.getQqnumber().length() >0){
            stringBuffer.append(" and qqnumber = "+users.getQqnumber());

        }
        if(users.getUsersex() != null && users.getUsersex().length() >0){
            stringBuffer.append(" and usersex = "+users.getUsersex());

        }
        if(users.getPhonenumber() != null && users.getPhonenumber().length() >0){
            stringBuffer.append(" and phonenumber = "+users.getPhonenumber());
        }

        return stringBuffer.toString();
    }

    //预更新 根据用户id找到要更新的用户
    @Override
    public Users selectUserByUserId(int userid) {
        Connection conn = null;
        Users users = null;//要返回user 所以在try外定义
        try{
            conn=JdbcUtils.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from users where userid = ? ");
            ps.setInt(1,userid);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                 users = new Users();
                 users.setUserid(resultSet.getInt("userid"));
                 users.setUsername(resultSet.getString("username"));
                 users.setUserpwd(resultSet.getString("userpwd"));
                 users.setUsersex(resultSet.getString("usersex"));
                 users.setPhonenumber(resultSet.getString("phonenumber"));
                 users.setQqnumber(resultSet.getString("qqnumber"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.closeConnection(conn);
        }
        return users;
    }


    //更新用户  传入来的参数用户是已经修改的用户
    @Override
    public void updateUserByUserid(Users users) {
        //
        Connection conn = null;
        try{
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("update users set username= ?, usersex= ?, phonenumber = ?,qqnumber= ? where userid = ?");
            ps.setString(1,users.getUsername());
            ps.setString(2,users.getUsersex());
            ps.setString(3,users.getPhonenumber());
            ps.setString(4,users.getQqnumber());
            ps.setInt(5,users.getUserid());
            ps.execute();
            conn.commit();//提交
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtils.rollbackConnection(conn);
        }finally {
            JdbcUtils.closeConnection(conn);
        }

    }

    //根据用户id删除用户id  DML操作 要有事业的概念
    @Override
    public void deleteUserByUserid(int userid) {
        Connection conn = null;
        try{
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("delete from users where userid = ? ");
            ps.setInt(1,userid);
            ps.execute();
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtils.rollbackConnection(conn);
        }finally {
            JdbcUtils.closeConnection(conn);
        }
    }
}
